package pl.koszolko.documentclustering.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pl.koszolko.documentclustering.dao.Author;
import pl.koszolko.documentclustering.dao.AuthorDao;
import pl.koszolko.documentclustering.dao.utils.DataBaseConnectionManager;
import pl.koszolko.documentclustering.dao.utils.DataBaseUtils;

public class JdbcAuthorDao implements AuthorDao {
	private static final String INSERT = "insert into AUTORZY VALUES(?,?)";
	private static final String UPDATE = "update AUTORZY set IMIE_NAZWISKO = ? where ID_AUTORA = ?";
	private static final String DELETE = "delete from AUTORZY where ID_AUTORA = ?";
	private static final String FIND_ALL = "select ID_AUTORA, IMIE_NAZWISKO from AUTORZY";
	private static final String FIND_BY_NAME = "select ID_AUTORA, IMIE_NAZWISKO from AUTORZY where IMIE_NAZWISKO = ?";
	private static final String SEQ = "select nextval('autorzy_seq')";

	private DataBaseConnectionManager connectionManager;
	
	public JdbcAuthorDao() {	
	}
	
	public JdbcAuthorDao(DataBaseConnectionManager connectionManager) {
		this.connectionManager = connectionManager;
	}
	
	@Override
	public void insert(Author author) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			Long id = DataBaseUtils.getNextValFromSeq(conn, SEQ);
			author.setId(id);
			
			PreparedStatement ps = conn.prepareStatement(INSERT);
			ps.setLong(1, author.getId());
			ps.setString(2, author.getName());
			ps.executeUpdate();
			
			ps.close();
		} catch (SQLException e) {
			//e.printStackTrace();
			author.setId(getIdFromDB(author.getName()));
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	@Override
	public void update(Author author) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			PreparedStatement ps = conn.prepareStatement(UPDATE);
			ps.setString(1, author.getName());
			ps.setLong(2, author.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	@Override
	public void delete(Long authorId) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			PreparedStatement ps = conn.prepareStatement(DELETE);
			ps.setLong(1, authorId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	@Override
	public List<Author> findAll() {
		List<Author> authors = new ArrayList<Author>();
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();		
			PreparedStatement ps = conn.prepareStatement(FIND_ALL);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Author author = new Author();
				author.setId(rs.getLong(1));
				author.setName(rs.getString(2));
				authors.add(author);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		return authors;
	}

	private Author findByName(String name) {
		Author author = null;
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();		
			PreparedStatement ps = conn.prepareStatement(FIND_BY_NAME);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()) {
				author = new Author();
				author.setId(rs.getLong(1));
				author.setName(rs.getString(2));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		return author;
	}
	
	private Long getIdFromDB(String name) {
		Author author = findByName(name);
		
		if(author != null)
			return author.getId();
		
		return null;
	}

	public void setConnectionManager(DataBaseConnectionManager connectionManager) {
		this.connectionManager = connectionManager;
	}

	public DataBaseConnectionManager getConnectionManager() {
		return connectionManager;
	}

}
